package com.deng.etl.excel.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.deng.common.SQLHelper;
import com.deng.etl.excel.IExcelRule;
import com.deng.maintenance.vo.VoteValueVo2;
import com.deng.util.DateTools;

public class GongZuoJiXiaoGeRenPingFenImpl extends SQLHelper implements
		IExcelRule {

	private Map<String, Integer> empMap = new HashMap<String, Integer>();
	private Map<String, Integer> voteMap = new HashMap<String, Integer>();
	public void importData(Workbook wb, String type_name,String id) {
		try{
			Connection conn = getConnect();
//			List<VoteValueVo2> l = new ArrayList<VoteValueVo2>();
//			String sql = "select * from VOTE_VS_DISPENSE,VOTE_DICT2" +
//					"	where dispense_id = 10  and frq_code = '0004'" +
//					"	and VOTE_VS_DISPENSE.vote_id = VOTE_DICT2.id";
//			ResultSet rs = queryData(conn, sql);
//			while(rs.next()){
//				VoteValueVo2 vo = new VoteValueVo2();
//				vo.vote_id = rs.getInt("vote_id");
//				vo.organization_id = rs.getInt("dispense_id");
//				vo.frq_code = rs.getString("frq_code");
//				vo.vote_name = rs.getString("name");
//				l.add(vo);
//			}
			
//			if("0".equals(update)){	//纯插入
//				conn.setAutoCommit(false);
//				String delSQL = "delete "+table+" where stat_time = '"+stat_time+"' and dispense_id = "+id;
//				updateData(conn, delSQL);//删除stat_Time当天
				
				Sheet sh=wb.getSheet(0);
				int rownum = sh.getRows();
				
//				String insertSQL = "insert into VOTE_VALUE2 (vote_id,dispense_id,score,frq_code,employee_id," +
//						"	create_time,modify_time,stat_time,valid)" +
//						"	values(?,?,?,?,?,?,?,?,0)";
//				PreparedStatement pst =conn.prepareStatement(insertSQL);
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);
					if(c1 == null){
						continue;
					}
					if(c1.getContents() == null || "".equals(c1.getContents())){
						continue;
					}
					Cell c2 = sh.getCell(1,i);//y
					Cell c3 = sh.getCell(2,i);//shijian
					Cell c4 = sh.getCell(3,i);//xingming
					Cell c5 = sh.getCell(4,i);//gonghao
					Cell c6 = sh.getCell(5,i);//fenshu
					
					String vote_name = c1.getContents().trim();
					String[] s = getTime(c2.getContents(),c3.getContents());
					String frq_code = s[1];
					Integer vote_id = getVoteIdByName(conn,vote_name,id,frq_code);
					if(vote_id == null || vote_id == 0){
						System.out.println(vote_id+"  不是 "+id+" 的评价项目！");
						continue;
					}
					String stat_time = s[0];
					BigDecimal score = new BigDecimal(0);
					try{
						score = new BigDecimal(c6.getContents().trim());
					}catch(Exception ex){
						ex.printStackTrace();
					}
					String create_time = DateTools.dtsWithTime(new Date());
					Integer employee_id = getEmployeeIdByWorkId(conn, c5.getContents());
					
					String sql = "select score,valid from VOTE_VALUE2 " +
							"	where vote_id = "+vote_id+
							" 	and dispense_id = "+id+
							" 	and frq_code = '"+frq_code+"'"+
							"	and stat_time = '"+stat_time+"'"+
							"	and employee_id = "+employee_id;
					PreparedStatement pst=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
					ResultSet rs=pst.executeQuery();
					if(rs.next()){
						int valid = rs.getInt("valid");
						if(valid == 1){//已经审核
							continue ;
						}else{//未审核
							rs.updateBigDecimal(1, score);
							rs.updateRow();
						}
					}else{
						sql = "insert into VOTE_VALUE2 (vote_id,dispense_id,score,frq_code,employee_id," +
						"	create_time,modify_time,stat_time,valid) " +
						"	values("+vote_id+","+id+","+score+",'"+frq_code+"',"+employee_id+"," +
						"	'"+create_time+"','"+create_time+"','"+stat_time+"',0)";
						updateData(conn, sql);
					}					
				}
				freeConnection(conn);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	private Integer getEmployeeIdByWorkId(Connection conn,String work_id){
		try{
			if(work_id == null || "".equals(work_id)){
				return 0;
			}
			if(empMap == null || empMap.size() == 0){
				String sql = "select employee_id,work_id from t_sys_stat_employee";
				ResultSet rs = queryData(conn, sql);
				while(rs.next()){
					empMap.put(rs.getString("work_id"), rs.getInt("employee_id"));
				}
			}
			return empMap.get(work_id);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
		
	}
	private Integer getVoteIdByName(Connection conn,String name,String id,String frq_code){
		try{
			if(name == null || "".equals(name)){
				return 0;
			}
			if(voteMap == null || voteMap.size() == 0){
				String sql = "select id,name from VOTE_DICT2" +
						"	where id in(select vote_id from VOTE_VS_DISPENSE " +
						"	where dispense_id = "+id+" and frq_code = '"+frq_code+"')";
				ResultSet rs = queryData(conn, sql);
				while(rs.next()){
					voteMap.put(rs.getString("name"), rs.getInt("id"));
				}
			}
			return voteMap.get(name);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
		
	}
	private String[] getTime(String year,String time){
		try{
			if(time == null || "".equals(time)){
				return new String[]{"1900-1-1","0004"};
			}
			time = time.trim();
			if("".equals(time)){
				return new String[]{year+"-1-1","0001"};
			}else if("上半年".equals(time)){
				return new String[]{year+"-1-1","0002"};
			}else if("下半年".equals(time)){
				return new String[]{year+"-6-1","0002"};
			}else if("一季度".equals(time)){
				return new String[]{year+"-1-1","0003"};
			}else if("二季度".equals(time)){
				return new String[]{year+"-4-1","0003"};
			}else if("三季度".equals(time)){
				return new String[]{year+"-7-1","0003"};
			}else if("四季度".equals(time)){
				return new String[]{year+"-10-1","0003"};
			}else{
				return new String[]{year+"-"+time.replace("月", "")+"-1","0004"};
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return new String[]{"1900-1-1","0004"};
		
	}

}
